package daoMySQL;

import java.sql.*;
import java.util.ArrayList;
import javax.swing.JOptionPane;
import daoDataFactory.DbObject;
import daoInterface.DaoIGerecht;
import domijn.Categorie;
import domijn.Gerecht;
import domijn.Menu;

public class MySQLGerecht extends DbObject implements DaoIGerecht {

	private Statement stmt = null;
	private ResultSet rs = null;
	private Menu m;
	private Categorie c;
	private Gerecht g;
	
	private static MySQLGerecht instance;
	
	public static DaoIGerecht instance(){
		if (instance == null) {
			 instance = new MySQLGerecht();
		}
		return instance;
	}

        @Override
        public void save(Gerecht g) {
        	String sqlFind =  "SELECT * FROM `gerecht` WHERE `MenuID` = "+ g.getMenu().getMenuID() + " AND `GerechtNummer` = "+ g.getGrNewNumber() +"";
        	try {
        		stmt = openConnection().createStatement();
        		rs = stmt.executeQuery(sqlFind);
        		rs.next();
        		
        		if (rs.getInt(1) == 0){
        		}
        		else{
        			JOptionPane.showMessageDialog(null, " Het gerecht in menu : " + g.getMenu().getMenuName() + ""
        											  + " met het gerecht nummer " + g.getGrNewNumber() +" bestaat al" );
        		}
        		} 
        	catch (SQLException sql2) {
        			try{
        				stmt = openConnection().createStatement();
        				stmt.executeUpdate("INSERT INTO"
        						+ "		`gerecht`(MenuID,GerechtNummer,GerechtNaam,GerechtPrijs,GerechtOmschrijving)"
        						+ "	VALUES("
        						+ "		'"+ g.getMenu().getMenuID()+"',"
        						+ "		'"+ g.getGrNewNumber()+"',"
        						+ "		'"+ g.getGrName()  +"',"
        						+ "		'"+ g.getGrPrice() +"',"
        						+ "		'"+ g.getGrDiscr() +"'"
        						+ ")");	
   	    				}
        			catch(SQLException sql1){
   	    				}
        			try{
        				stmt = openConnection().createStatement();
        				stmt.executeUpdate("INSERT INTO"
        						+ "		`mcc`(MenuID, CategorieID, GerechtID)"
        						+ "	VALUES("
        						+ "		        '"+ g.getMenu().getMenuID()+"',"
        						+ "				'"+ g.getCat().getCategorieID()+"',"
        						+ " (SELECT `GerechtID` from `gerecht` "
        						+ " where `gerecht`.`GerechtNummer` = "+g.getGrNewNumber()
        						+ " AND  `gerecht`.`MenuID` = " + g.getMenu().getMenuID() +"))");
          				}
        			catch(SQLException e){
             			}
        			}
        		}
                
        @Override
        public void update(Gerecht g) {
		String sqlUpdate = "UPDATE"
				+ "		`gerecht`"
				+ "	SET"
				+ "		`gerecht`.`GerechtNummer`    	='"+ g.getGrNewNumber()+"',"
				+ "		`gerecht`.`GerechtNaam`  		='"+ g.getGrName() +"',"
				+ "     `gerecht`.`GerechtPrijs` 		='"+ g.getGrPrice() +"',"
				+ "		`gerecht`.`GerechtOmschrijving` ='"+ g.getGrDiscr() +"'"
				+ "	WHERE"
				+ "		`gerecht`.`MenuID` = '" + g.getMenu().getMenuID()+"'"
				+ " AND" 
				+ "		`gerecht`.`GerechtNummer` ='"+ g.getGrOldNumber()+"'";
		
		try{
			stmt = openConnection().createStatement();
			stmt.executeUpdate(sqlUpdate);
					}
		catch(Exception e1){
		}}

        
        
        
        @Override
        public void delete(Gerecht g) {
        	String sqlMccDelete =  "DELETE FROM "
        			+ " 	`mcc` "
        			+ "	WHERE "
        			+ "		`mcc`.`MenuID` = "+ g.getMenu().getMenuID() + ""
        			+ " AND "
        			+ "		`mcc`.`gerechtID` = "
					+ " (SELECT `GerechtID` from `gerecht` "
					+ " where `gerecht`.`GerechtNummer` = "+g.getGrNewNumber()
					+ " AND  `gerecht`.`MenuID` = " + g.getMenu().getMenuID() +")";
		try {
			stmt = openConnection().createStatement();
			stmt.executeUpdate(sqlMccDelete);
			JOptionPane.showMessageDialog( null, "Gerecht is verwijderd!");
					} 
		catch (SQLException e2) {
			System.out.println(e2);
		}
    	String sqlGerechtDelete =  "DELETE FROM "
    			+ " 	`gerecht` "
    			+ "	WHERE "
    			+ "		`gerecht`.`MenuID` = "+ g.getMenu().getMenuID() + ""
    			+ " AND "
    			+ "		`gerecht`.`GerechtNummer` = "+ g.getGrOldNumber() +"";
    	try {
    		stmt = openConnection().createStatement();
    		stmt.executeUpdate(sqlGerechtDelete);
    		JOptionPane.showMessageDialog( null, "Gerecht is verwijderd!");
    				} 
    	catch (SQLException e2) {
    	}
        }

        
        
        
		@Override
		public void find(Gerecht gerecht) {
			String sqlFind =  " SELECT * "
							+ " FROM `gerecht` "
							+ " WHERE `MenuID` = "+ m.getMenuID() + ""
							+ " AND `GerechtNummer` = "+ g.getGrNewNumber() + "";
			
			try {
        		stmt = openConnection().createStatement();
        		rs = stmt.executeQuery(sqlFind);
        		rs.next(); 
    			g.setGrNewNumber(rs.getInt(3));
    			g.setGrName(rs.getString(4)) ;
    			g.setGrPrice(rs.getString(5));
    			g.setGrDiscr(rs.getString(6));
			}
			catch (SQLException e){
			}
		}
		
        @Override
        public ArrayList<Gerecht> loadall() {
        	ArrayList<Gerecht>gerechten  = new ArrayList<>();
        	String sqlLoadAll = "SELECT"
        		+ "		`menu`.`MenuNaam`,"
				+ "		`categorie`.`CategorieNaam`,"
				+ "  	`gerecht`.`GerechtNummer`,"
				+ "		`gerecht`.`GerechtNaam`, "
				+ "		`gerecht`.`GerechtPrijs`, "
				+ "		`gerecht`.`GerechtOmschrijving`" 
				+ " FROM "
				+ "		`mcc` "
				+ "	INNER JOIN " 
				+ "		`menu` "
				+ " ON "
				+ "		`mcc`.`MenuID` = `menu`.`MenuID`" 
				+ "	INNER JOIN " 
				+ "		`categorie` "
				+ " ON "
				+ "		`mcc`.`CategorieID` = `categorie`.`CategorieID`" 
				+ "	INNER JOIN " 
				+ "		`gerecht` "
				+ " ON "
				+ "		`mcc`.`GerechtID` = `gerecht`.`GerechtID`"
				+ " ORDER BY `menu`.`MenuNaam`, `gerecht`.`GerechtNummer` " ;
		try {
			stmt = openConnection().createStatement();
			rs = stmt.executeQuery(sqlLoadAll);
			while (rs.next()) {
				m = new Menu(rs.getString(1));
				c = new Categorie(rs.getString(2));
				g = new Gerecht(m,c,0, 0,rs.getInt(3), rs.getString(4),rs.getString(5),rs.getString(6));
				gerechten.add(g);
			}}catch (SQLException s) {
			}
		return gerechten;
        }

        @Override
        public ArrayList<Gerecht> loadGBA() {
        	ArrayList<Gerecht>gerechten  = new ArrayList<>();
        	String sqlLoadgba = "SELECT"
				+ "		`categorie`.`CategorieNaam`,"
				+ "  	`gerecht`.`GerechtNummer`,"
				+ "		`gerecht`.`GerechtNaam`, "
				+ "		`gerecht`.`GerechtPrijs`, "
				+ "		`gerecht`.`GerechtOmschrijving`" 
				+ " FROM "
				+ "		`mcc` "
				+ "	INNER JOIN " 
				+ "		`categorie` "
				+ " ON "
				+ "		`mcc`.`CategorieID` = `categorie`.`CategorieID`" 
				+ "	INNER JOIN " 
				+ "		`gerecht` "
				+ " ON "
				+ "		`mcc`.`GerechtID` = `gerecht`.`GerechtID`" 
				+ "	WHERE "
				+ "		`mcc`.`MenuID` = 1"
				+ " ORDER BY `gerecht`.`GerechtNummer`"; 
		try {
			stmt = openConnection().createStatement();
			rs = stmt.executeQuery(sqlLoadgba);
			while (rs.next()) {
				c = new Categorie(rs.getString(1));
				g = new Gerecht(m, c, 0,0,rs.getInt(2), rs.getString(3),rs.getString(4),rs.getString(5));
				gerechten.add(g);
			}}catch (SQLException s) {
			}
		return gerechten;
	}

        @Override
        public ArrayList<Gerecht> loadGBR() {
        	ArrayList<Gerecht>gerechten  = new ArrayList<>();
        	String sqlLoadgbr = "SELECT"
    				+ "		`categorie`.`CategorieNaam`,"
    				+ "  	`gerecht`.`GerechtNummer`,"
    				+ "		`gerecht`.`GerechtNaam`, "
    				+ "		`gerecht`.`GerechtPrijs`, "
    				+ "		`gerecht`.`GerechtOmschrijving`" 
    				+ " FROM "
    				+ "		`mcc` "
    				+ "	INNER JOIN " 
    				+ "		`categorie` "
    				+ " ON "
    				+ "		`mcc`.`CategorieID` = `categorie`.`CategorieID`" 
    				+ "	INNER JOIN " 
    				+ "		`gerecht` "
    				+ " ON "
    				+ "		`mcc`.`GerechtID` = `gerecht`.`GerechtID`" 
    				+ "	WHERE "
    				+ "		`mcc`.`MenuID` = 2"
    				+ "  ORDER BY `gerecht`.`GerechtNummer`"; 
		try {
			stmt = openConnection().createStatement();
			rs = stmt.executeQuery(sqlLoadgbr);
			while (rs.next()) {
				c = new Categorie(rs.getString(1));
				g = new Gerecht(m, c, 0,0,rs.getInt(2), rs.getString(3),rs.getString(4),rs.getString(5));
				gerechten.add(g);
			}}catch (SQLException s) {
			}
		return gerechten;
	}

  
}